package cn.schina.dbfw.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;

import cn.schina.dbfw.dao.DaoConn;
import cn.schina.dbfw.dao.JDBCConnection;

public class DBObjectParamsDao {
	ArrayList<DBObjectsParam> objectParams = new ArrayList<DBObjectsParam>();
	JDBCConnection conn = null;
	String sql;
	int baselineid;
	int first_flag = 0;
	public ArrayList<DBObjectsParam> getDBObjectParams() throws SQLException
	{
		conn = DaoConn.createDBConnection();
		PreparedStatement statement = conn.getConnection()
			.prepareStatement("select distinct (case when obj1.owner='' then '[ANY]' else obj1.owner end) owner,obj1.object_name " +
					"from( " +
					"select obj.database_id,obj.owner,obj.object_name " +
					"from objects obj " +
					"where obj.object_type=2 " +
					"group by obj.database_id,obj.owner,obj.object_name" +
					") obj1,(select distinct sql_sess.sqlid,sess.db_user " +
					"from sql_sessions sql_sess,sessionlog_event sess " +
					"where sess.source_id=? and " +
					"sess.db_server=? and " +
					"sess.connect_time between ? and ? and " +
					"sess.db_instance_name=? and " +
					"sql_sess.session_id=sess.session_id) sql2 " +
					"where	obj1.database_id=sql2.sqlid " +
					"order by owner,object_name"
					);
		// 第1组参数 
		statement.setInt(1, 1);
		statement.setString(2, "192.168.1.24");
		statement.setString(3, "2012-12-20");
		statement.setString(4, "2012-12-26");
		statement.setString(5, "orcl24");
		
		ResultSet resultSet = statement.executeQuery();
		while(resultSet.next())
		{
			DBObjectsParam _objectparam = new DBObjectsParam();
			_objectparam.setOwnername(resultSet.getString("owner"));
			_objectparam.setObjectname(resultSet.getString("object_name"));
			objectParams.add(_objectparam);
		}
		statement.close();
		return objectParams;
	}
	
	public ArrayList<DBObjectsParam> getDBObjectParams(DBWithDbfw p_dbwithdbfw,DateParam p_date) throws SQLException
	{
		Iterator<Dbfw> it;
		conn = DaoConn.createDBConnection();
		sql = "select distinct (case when obj1.owner='' then '[ANY]' else obj1.owner end) owner,obj1.object_name " +
				"from( " +
				"select obj.database_id,obj.owner,obj.object_name " +
				"from objects obj " +
				"where obj.object_type=2 " +
				"group by obj.database_id,obj.owner,obj.object_name) obj1,( " +
				"select distinct sql_sess.sqlid,sess.db_user " +
				"from sql_sessions sql_sess,sessionlog_event sess ";
		//where sess.source_id=1 and
		sql = sql + "where sess.source_id in (0";
		it = p_dbwithdbfw.getDbfwArray().iterator();
		while(it.hasNext())
		{
			Dbfw tmp_dbfw = it.next();
			sql = sql + ","+tmp_dbfw.getInstid();
		}
		sql = sql + ") and ";
		//sess.db_server='192.168.1.24' and
		sql = sql + "sess.db_server='"+p_dbwithdbfw.getDb_address()+"' and ";
		//sess.connect_time between '2012-12-20' and '2012-12-26' and
		sql = sql + "sess.connect_time between '"+p_date.startDate+"' and '"+p_date.endDate+"' and ";
		//sess.db_instance_name='orcl24' and
		sql = sql + "UPPER(sess.db_instance_name)='"+p_dbwithdbfw.getName()+"' and ";
		sql = sql + "sql_sess.session_id=sess.session_id ) sql2 " +
				"where	obj1.database_id=sql2.sqlid " +
				"order by owner,object_name";
		//System.out.println(sql);
		PreparedStatement statement = conn.getConnection()
			.prepareStatement(sql);
		ResultSet resultSet = statement.executeQuery();
		while(resultSet.next())
		{
			DBObjectsParam _objectparam = new DBObjectsParam();
			_objectparam.setOwnername(resultSet.getString("owner"));
			_objectparam.setObjectname(resultSet.getString("object_name"));
			objectParams.add(_objectparam);
		}
		statement.close();
		return objectParams;
	}
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		ArrayList<DBObjectsParam> objectParams = new ArrayList<DBObjectsParam>();
		DBObjectParamsDao _objectparamdao;
		DBWithDbfw p_dbwithdbfw = new DBWithDbfw();
		DateParam p_date = new DateParam();
		Dbfw _dbfw;
		p_dbwithdbfw.setId(1);
		p_dbwithdbfw.setName("orcl24");
		p_dbwithdbfw.setDbtype("1");
		p_dbwithdbfw.setDbversion("10020001");
		p_dbwithdbfw.setDialect(1);
		p_dbwithdbfw.setDb_address("192.168.1.24");
		p_dbwithdbfw.setDb_port(1521);
		p_dbwithdbfw.setName_forprotected("test");
		_dbfw = new Dbfw();
		_dbfw.setInstid(1);
		_dbfw.setName("dbfw24");
		_dbfw.setBaselineid(174);
		_dbfw.setAddress("192.168.1.24");
		p_dbwithdbfw.addDbfwForDB(_dbfw);
		p_date.setStartDate("2012-12-20");
		p_date.setEndDate("2012-12-26");
		_objectparamdao = new DBObjectParamsDao();
		try {
			objectParams = _objectparamdao.getDBObjectParams(p_dbwithdbfw,p_date);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}
